package etl.db;

import java.sql.*;

public class TestSQLServerDB {

	private Connection conn;
	private Statement stmt;
	private String url = "jdbc:jtds:sqlserver://localhost:1433/SFA_APP";
	private String driverName = "net.sourceforge.jtds.jdbc.Driver";
	private String username = "sa";
	private String pwd = "sa123";

	public TestSQLServerDB() {
	}

	private void pageListMsSql(String strSql, int pageIndex, int pageSize) throws Exception {
		
		StringBuffer sbSql = new StringBuffer();
		Connection conn = null;
		CallableStatement cstmt = null;
		ResultSet rs = null;

		try {
			conn = getConnection();
			sbSql.append("{call GetPage(?,?,?,?)}");
			cstmt = conn.prepareCall(sbSql.toString());
			cstmt.setObject(1, strSql);
			cstmt.setInt(2, pageIndex);
			cstmt.setInt(3, pageSize);
			cstmt.registerOutParameter(4, Types.INTEGER);
			cstmt.execute();
			boolean flag = cstmt.getMoreResults();
			System.out.println(flag);
			rs = cstmt.getResultSet();
			if (rs != null) {
				ResultSetMetaData rsmd;
				rsmd = rs.getMetaData();

				while (rs.next()) {
					for (int a = 1; a <= rsmd.getColumnCount(); a++) {
						System.out.println(rs.getObject(a));
					}

				}
			}
		} catch (Exception e) {
			System.out.println("sql=" + strSql.toString());
		} finally {
			if (cstmt != null) {
				cstmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		}
	}

	/**
	 * <p>
	 * 通过Microsoft JDBC驱动获得数据库连接
	 * </p>
	 * 
	 * @return Connection
	 * @exception ClassNotFoundException
	 *                , SQLException
	 */
	public Connection getConnection() {
		try {
			Class.forName(driverName);
			if (conn == null || conn.isClosed())
				conn = DriverManager.getConnection(url, username, pwd);
		} catch (ClassNotFoundException ex) {
			ex.printStackTrace();
		} catch (SQLException ex) {
			ex.printStackTrace();
		}

		return conn;
	}
	
	public static void main(String[] args) throws Exception {
		TestSQLServerDB db = new TestSQLServerDB();
		
		Connection conn = db.getConnection();
		
		String sql = "select @@version";
		
		Statement stmt = conn.createStatement();
		ResultSet rSet = stmt.executeQuery(sql);
		while (rSet.next()) {
			String db_version = rSet.getString(1);
			System.out.println(db_version);
		}
		
		sql = "select  r.*,g.name as groupname from   ums_user  r left join ums_group  g   on r.group_id=g.id where 1=1   ORDER BY r.logon_time DESC";
		db.pageListMsSql(sql, 1, 2);
		System.out.println("connect Sqlserver 2000 DB successfully!");
	}

}
